IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveMaxMSSQLSchedJobHistoryId' AND type = 'P')
     DROP PROCEDURE dbo.SaveMaxMSSQLSchedJobHistoryId
GO

CREATE PROCEDURE dbo.SaveMaxMSSQLSchedJobHistoryId
     @SrvrInstncId int = NULL -- Optional Parameter, leave NULL for an update to ALL.
AS
/*
 *
 * SaveMaxMSSQLSchedJobHistoryId
 *
 * Purpose:      To Calculate and save the MaxSchedJobHistoryId
 *               for the specified server.
 *
 * Inputs:       @SrvrInstncId
 *
 * Assumes:      
 *
 * Effects:      Database is updated, or record is added
 *
 * Returns:      Nothing
 *
 * Note(s):      As a paranoia step if you force @SrvrInstncId to NULL, 
 *               it will update ALL Max Job History Ids for any server instance
 *               that has history.
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 10/31/2011, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @ParameterId int

     -- We need this everywhere so we set it early.
     SELECT @ParameterId  = dbo.GetParameterIdByParameterCd( 'MaxJobHistoryId' )
     
     -- Determine if we are doing one or all.
     IF ( @SrvrInstncId IS NOT NULL )
     BEGIN
          DECLARE @MaxJobHistoryId  bigint,
                  @msg              varchar(80)

          -- Determine max id.
          SELECT @MaxJobHistoryId = ISNULL(MAX(JobHistoryId),0)
          FROM   dbo.SrvrInstncSchedJobHistoryMSSQL
          WHERE  SrvrInstncId = @SrvrInstncId

          -- Determine if record exists
          IF ( dbo.GetParameterCdExistsBySrvrInstncId( @SrvrInstncId, 
                                                       'MaxJobHistoryId' ) = 1 )
          BEGIN -- Record already exists so we do update
               UPDATE dbo.SrvrInstncEAV
               SET    ParameterValue = @MaxJobHistoryId,
                      LastModDt      = CURRENT_TIMESTAMP 
               WHERE  SrvrInstncId     = @SrvrInstncId 
               AND    ParameterId      = @ParameterId

               IF ( @@ROWCOUNT != 1 )
               BEGIN
                    SELECT @msg = 'The update of the MaxJobHistoryId for ServerInstanceId: '
                                + CONVERT( varchar(11), @SrvrInstncId )
                                + ' Failed'

                    RAISERROR( @msg, 16, 1 )
               END
          END
          ELSE
          BEGIN
               INSERT dbo.SrvrInstncEAV
                    ( SrvrInstncId,
                      ParameterId,
                      ParameterValue )
               SELECT @SrvrInstncId,
                      @ParameterId,
                      @MaxJobHistoryId 

               IF ( @@ROWCOUNT != 1 )
               BEGIN
                    SELECT @msg = 'The insert of the MaxJobHistoryId for ServerInstanceId: '
                                + CONVERT( varchar(11), @SrvrInstncId )
                                + ' Failed'

                    RAISERROR( @msg, 16, 1 )
               END
          END
          
          SELECT SrvrInstncId    = @SrvrInstncId, 
                 MaxJobHistoryId = @MaxJobHistoryId
     END
     ELSE 
     BEGIN -- @ServerInstanceId is NULL, do all of them.
          DECLARE @Server TABLE ( SrvrInstncId    int           NOT NULL,
                                  MaxJobHistoryId varchar(8000) NOT NULL )

          -- Get all server ids and maximums
          INSERT @Server 
               ( SrvrInstncId,
                 MaxJobHistoryId )
          SELECT SrvrInstncId,
                 ISNULL(MAX(JobHistoryId),0)
          FROM   dbo.SrvrInstncSchedJobHistoryMSSQL
          GROUP BY SrvrInstncId

          -- Update any that already exist, we do this first on purpose.
          UPDATE dbo.SrvrInstncEAV
          SET    ParameterValue = s.MaxJobHistoryId,
                 LastModDt      = CURRENT_TIMESTAMP 
          FROM   dbo.SrvrInstncEAV sie INNER JOIN @Server s
                    ON sie.SrvrInstncId = s.SrvrInstncId
                   AND sie.ParameterId  = @ParameterId
          WHERE  sie.ParameterValue != s.MaxJobHistoryId 
          OR     sie.ParameterValue IS NULL

          -- Insert the new ones, making extra sure that we don't add a dupe
          INSERT dbo.SrvrInstncEAV
               ( SrvrInstncId,
                 ParameterId,
                 ParameterValue )
          SELECT s.SrvrInstncId,
                 @ParameterId,
                 s.MaxJobHistoryId 
          FROM   @Server s
          WHERE  NOT EXISTS ( SELECT 'X' 
                              FROM   dbo.SrvrInstncEAV sie
                              WHERE  sie.SrvrInstncId = s.SrvrInstncId
                              AND    sie.ParameterId  = @ParameterId )

          SELECT SrvrInstncId, 
                 MaxJobHistoryId = CONVERT( bigint, MaxJobHistoryId )
          FROM   @Server 
     END

END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Calculate and save the maximum JobHistoryId in SrvrInstncSchedJobHistoryMSSQL for a specified server instance.  This is used to determine what records are retrieved during the next collection run so that the whole table is not collected and only the new records (as far as the SrvrInstncMSSQLSchedJobHistory is concerned) are retrieved.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveMaxMSSQLSchedJobHistoryId;
GO
